package Class;

import java.awt.Component;
import java.awt.Frame;
import java.io.File;
import java.sql.*;

import java.text.DateFormat;
import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Vector;
import javax.jws.WebParam.Mode;
import javax.swing.DefaultCellEditor;
import javax.swing.JCheckBox;
import javax.swing.ImageIcon;
import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JDialog;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JTable;
import javax.swing.JTextArea;
import javax.swing.JTextField;
import javax.swing.table.DefaultTableCellRenderer;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableCellRenderer;
import javax.swing.table.TableColumn;
import org.jdesktop.swingx.JXDatePicker;
import org.jdesktop.swingx.table.DatePickerCellEditor;

import com.sun.java.swing.plaf.motif.resources.motif;

import Form.Login;
import Form.Main;
import Support.International;
import Support.Mail;
import Support.ThreadWaiting;

public class Procedure {
	
	
	static int check;
	static ArrayList<String> listLoginName;
	
	//Check login name and password same as in database
	public static void checkLogin(String loginName,String password)
	{
		try
		{
			String sql = "{call checkLogin(?,?,?,?)}";
	        CallableStatement cstmt = ConnectDatabase.connection.prepareCall(sql);
	        cstmt.setString(1,loginName);
	        cstmt.setString(2,MD5.encryptMD5(password));
	        cstmt.registerOutParameter(3, java.sql.Types.INTEGER); //if info login right, return 1
	        cstmt.registerOutParameter(4, java.sql.Types.INTEGER); //id decentralization
	        cstmt.execute();
	        int result = cstmt.getInt(3);
	        int decentralization = cstmt.getInt(4);
	        if(result == 1)
	        {
	        	Employee emp = getMyInfo(loginName);
	        	Login.getLoginName().setText("");
	        	Login.getLoginPassword().setText("");
	        	Login.frLogin.setVisible(false);
	        	Login.frLogin.dispose();
	        	switch(decentralization)
	        	{
	        		case 0: new Main(0,emp); break;
	        		case 1: new Main(1,emp); break;
	        		case 2: new Main(2,emp); break;
	        		case 3: new Main(3,emp); break;
	        		case 4: new Main(4,emp); break;
	        		default: break;
	        	}
	        }
	        else Login.lbWarning.setVisible(true);
		} catch(Exception ex) {
			System.out.println(ex.toString());
		}
	}
	
	//Forgot password
	public static String forgotPassword(String loginName,int question,String answer,String mail)
	{
		String newPassword = "";
		try
		{
			String sql = "{call checkForgotPassword(?,?,?,?)}";
			CallableStatement cstmt = ConnectDatabase.connection.prepareCall(sql);
			cstmt.setString(1, loginName);
			cstmt.setInt(2, question);
			cstmt.setString(3,answer);
			cstmt.registerOutParameter(4, java.sql.Types.INTEGER);
			cstmt.execute();
			int check = cstmt.getInt(4);
			if(check == 1)
			{
//				JDialog dialog = new JDialog();
				ThreadWaiting thread = new ThreadWaiting();
//				thread.start();
				thread.show();
				boolean flgSuccess;
				newPassword = CreateAccount.randomPassword();
				flgSuccess = Mail.send(mail, loginName, newPassword);
				changePassword(loginName,newPassword);
				if(flgSuccess == true)
				{
//					dialog.setVisible(false);
//					dialog.dispose();
//					thread.interrupt();
					thread.hide();
					JOptionPane.showMessageDialog(null, "Successfully!");
				}
				else
					{
//						dialog.setVisible(false);
//						dialog.dispose();
//						thread.interrupt();
						thread.hide();
						JOptionPane.showMessageDialog(null,"Lost connection!", "Warning",JOptionPane.WARNING_MESSAGE);
					}
			}
			else
			{
				JOptionPane.showMessageDialog(null,"Your login name or your question/answer isn't right!!", "Warning",JOptionPane.WARNING_MESSAGE);
			}
		} catch(Exception ex) {
			System.out.println(ex.toString());
		}
		return newPassword;
	}
	
	//change Password
	public static void changePassword(String loginName,String newPassword)
	{
		try
		{
			String sql = "{call ChangePassword(?,?)}";
			CallableStatement cstmt = ConnectDatabase.connection.prepareCall(sql);
			cstmt.setString(1, loginName);
			cstmt.setString(2,MD5.encryptMD5(newPassword));
			cstmt.execute();
		} catch(Exception ex) {}
	}
	
	//Show my info: The info of employee connected
	public static Employee getMyInfo(String loginName)
	{
		Employee emp = new Employee();
		try
		{
			String sql = "{call ShowEmp(?)}";
			
			CallableStatement cstmt = ConnectDatabase.connection.prepareCall(sql);
	        cstmt.setString(1,loginName);
	        ResultSet resultSet = cstmt.executeQuery();
	        while(resultSet.next())
            {
	        	
		        emp.setId(Integer.parseInt(resultSet.getString("Emp_Id")));
		        emp.setName(resultSet.getString("Emp_Name"));
		        emp.setAddress(resultSet.getString("Emp_Address"));
		        emp.setPhone(Integer.parseInt(resultSet.getString("Emp_Phone")));
		        String getMail = resultSet.getString("Emp_Mail");
		        emp.setMail(getMail);		        
		        emp.setImage(resultSet.getString("Emp_Image"));
		        emp.setStatus(Integer.parseInt(resultSet.getString("Status_Id")));
		        emp.setDecentralization(Integer.parseInt(resultSet.getString("Decentralization_Id")));
		        emp.setLoginName(resultSet.getString("Login_Name"));
		        emp.setLoginPassword(resultSet.getString("Login_Password"));
		        emp.setQuestion(Integer.parseInt(resultSet.getString("S_Question")));
		        emp.setAnswer(resultSet.getString("S_Answer"));		    
		        Date timeCreated = resultSet.getDate("Time_Created");
		        String formatDate = new SimpleDateFormat("dd-MM-yyyy").format(new java.sql.Date(timeCreated.getTime()));
		        Date resultDate = new SimpleDateFormat("dd-MM-yyyy").parse(formatDate);
		        emp.setDateCreated(resultDate);
            }
	        
		} catch(Exception ex) {
		}
        return emp;
	}
	
	//Add Employee
	
	public static void IncreaseID()
	{
		try
		{
			CallableStatement IncID = ConnectDatabase.connection.prepareCall("{call IncreaseIDEmployee()}");
			IncID.execute();	
		} catch(Exception ex) {}
	}
	
	public static int AddEmpInfo(int id,String name,String address, int phone,String mail,String image, int status,int decentralization,String loginName,String password,int question, String answer, java.sql.Date date){
		try {	
			CallableStatement addEmpInfo = ConnectDatabase.connection.prepareCall("{call AddEmp(?,?,?,?,?,?,?,?,?,?,?,?,?)}");
			addEmpInfo.setInt(1, id);
			addEmpInfo.setString(2,name );
			addEmpInfo.setString(3, address);
			addEmpInfo.setInt(4, phone);
			addEmpInfo.setString(5, mail);
			addEmpInfo.setString(6,image);
			addEmpInfo.setInt(7,status);
			addEmpInfo.setInt(8, decentralization);
			addEmpInfo.setString(9, loginName);
			addEmpInfo.setString(10, password);
			addEmpInfo.setInt(11,question);
			addEmpInfo.setString(12,answer);
			addEmpInfo.setDate(13, date);
			addEmpInfo.execute();		
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 1;//after adding successfully, method will return 1
		
	}
	//Find Employee
		public static int FindEmp(String loginName){
			try {
				
	            CallableStatement findEmpInfo = ConnectDatabase.connection.prepareCall("{call FindEmp(?,?)}");		
	            findEmpInfo.setString(1, loginName);
	            findEmpInfo.registerOutParameter(2, java.sql.Types.INTEGER);
	            findEmpInfo.execute();
				check = findEmpInfo.getInt(2);
				
				
			} catch (Exception e) {
				// TODO Auto-generated catch block
				
			}
			if(check==1){
				return 1;//return 0 if login name found
			}
			else{
				return 0;//return 1 if not found
			}
			
		}
		
		//Delete Employee
		public static void DelEmp(String loginName){
			try{
		        CallableStatement delEmp = ConnectDatabase.connection.prepareCall("{call DelEmp(?)}");
		        delEmp.setString(1, loginName);
		        delEmp.execute();
		        File image = new File("emps/"+loginName+".jpg");
		        if(image.exists())
		        	image.delete();
		        CallableStatement delAtt = ConnectDatabase.connection.prepareCall("{call DeleteAttendance(?,?)}");
		        delAtt.setString(1, loginName);
		        DateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");  
				Date untilDay = formatter.parse(formatter.format(new Date().getTime()));
	            java.sql.Date sqlDateuntil = new java.sql.Date(untilDay.getTime());
	            delAtt.setDate(2,sqlDateuntil);
	            delAtt.execute();
			}catch(Exception e){
				System.out.println(e.toString());
			}    
	        
		}	

		public static void List_Emp(DefaultTableModel modelTable, JTable table,int option,String loginName,String mylogin,int decentralization,int status){
			try{
				listLoginName = new ArrayList<String>();
				CallableStatement call = ConnectDatabase.connection.prepareCall("{call List_Emp(?,?,?,?,?)}");
				call.setInt(1,option);
				call.setString(2,loginName);
				call.setString(3,mylogin);
				call.setInt(4,decentralization);
				call.setInt(5,status);	 
			
		         ResultSet result = call.executeQuery();
		         ResultSetMetaData meta = result.getMetaData();
		      
		         Vector colName = new Vector();
		         colName.addElement("ID");
		         colName.addElement("Login name");
		         colName.addElement("Name");
		         colName.addElement("Function");
		         colName.addElement("Status");
		         modelTable.setColumnIdentifiers(colName);
		           while(result.next()){
			        	   Vector row1 = new Vector();
			               for(int i=1;i<=meta.getColumnCount();i++){
			            	   row1.addElement(result.getString(i));                
			               }
			               listLoginName.add(result.getString(2));
			               modelTable.addRow(row1);  
		            }                          
		        table.setModel(modelTable);       
			}catch(Exception e){
				System.out.println(e.toString());
			}
		}
	
	public static void showEmpDetail(JLabel id,JTextField login,JTextField date,JTextField name, JTextArea address, JTextField phone,JTextField textmail,JComboBox mail,JComboBox function,JComboBox status,String loginname, JButton btnInfoAvatar ){
		try{
            CallableStatement call2 = ConnectDatabase.connection.prepareCall("{call ShowDetail(?)}");
            call2.setString(1, loginname );
		
	         ResultSet result = call2.executeQuery();
	         ResultSetMetaData meta = result.getMetaData();
	         while(result.next()){
	        	   	              
	                id.setText(result.getString(1));
	                login.setText(result.getString(2));
	                DateFormat newFormat = new SimpleDateFormat("dd-MM-yyyy");
	                String showDate = newFormat.format(result.getDate(3).getTime());
	                date.setText(showDate);
	                name.setText(result.getString(4));
	                address.setText(result.getString(5));
	                phone.setText(result.getString(6));
	                String getMail = result.getString(7);
	                String showMail = getMail.substring(0, getMail.lastIndexOf('@'));
	                String subMail = getMail.substring(getMail.lastIndexOf('@'), getMail.length());
	                textmail.setText(showMail);
	                if(subMail.trim().equals("@gmail.com")){
	                	mail.setSelectedIndex(0);
	                }else{
	                	mail.setSelectedIndex(1);
	                }
	                function.setSelectedItem(result.getString(8));
	                status.setSelectedItem(result.getString(9));
	                String image = result.getString(10);
	                ImageIcon icon;
	        		if(image != null)
	        			icon = new ImageIcon("emps/"+image);
	        		else icon = new ImageIcon("images/anonymous.png");
	        		btnInfoAvatar.setIcon(icon);  
	            }  
		}catch(Exception e){
			
		}
	}
	
	public static void UpdateInfo(String login,String name, String address, int phone,String mail,String image,int function,int status){
		try{
	        CallableStatement call3 = ConnectDatabase.connection.prepareCall("{call UpdateInfo(?,?,?,?,?,?,?,?)}");
	        call3.setString(1,login);       
	        call3.setString(2,name);
	        call3.setString(3,address);
	        call3.setInt(4,phone);
	        call3.setString(5,mail);
	        call3.setString(6,image);
	        call3.setInt(7,function);
	        call3.setInt(8,status);
	        call3.execute();
	        DateFormat formatter = new SimpleDateFormat("dd-MM-yyyy"); 
            Date getdate = (Date) formatter.parse((formatter.format(new Date().getTime())));
	        if(status != 3)
	        {
	        	CallableStatement callCheck = ConnectDatabase.connection.prepareCall("{call checkIntoDatabase(?,?,?)}");
	        	callCheck.setString(1,login);
	        	callCheck.setDate(2, new java.sql.Date(getdate.getTime()));
	        	callCheck.registerOutParameter(3, java.sql.Types.INTEGER);
	        	callCheck.execute();
	        	int check = callCheck.getInt(3);
	        	if(check == 1)
	        	{
	        		if(status != 2)
	        		{
				        PreparedStatement updateStatusAtt = ConnectDatabase.connection.prepareCall("UPDATE Attendance SET Before_Status = ?, Absence = ? WHERE Login_Name = ?");
				        updateStatusAtt.setInt(1,status);
				        if(status == 2)
				        	updateStatusAtt.setInt(2,1);
				        else updateStatusAtt.setInt(2,0);
				        updateStatusAtt.setString(3, login);
				        updateStatusAtt.execute();
	        		} else {
	        			PreparedStatement updateStatusAtt = ConnectDatabase.connection.prepareCall("UPDATE Attendance SET Absence = ? WHERE Login_Name = ?");
				        updateStatusAtt.setInt(1,1);
				        updateStatusAtt.setString(2, login);
				        updateStatusAtt.execute();
	        		}
	        	} else {
	        		addEmpInAttendance(login);
	        		PreparedStatement updateStatusAtt = ConnectDatabase.connection.prepareCall("UPDATE Attendance SET Absence = ? WHERE Login_Name = ?");
			        if(status == 2)
			        	updateStatusAtt.setInt(1,1);
			        else updateStatusAtt.setInt(1,0);
			        updateStatusAtt.setString(2,login);
			        updateStatusAtt.execute();
	        	}
	        } else { 
	        	PreparedStatement deleteAtt = ConnectDatabase.connection.prepareCall("DELETE Attendance WHERE Login_Name = ? AND Absence_Date = ?");
	        	deleteAtt.setString(1, login);
                deleteAtt.setDate(2, new java.sql.Date(getdate.getTime()));
	        	deleteAtt.execute();
	        }
		}catch(Exception e){
			System.out.print(e.toString());
		}
		
	}
	
	//=====Attendance
	
	public static void updateAttendance(JTable tableAttendance,JLabel date){
		try
		{
			DateFormat sdf = new SimpleDateFormat("dd-MM-yyyy");
			for(int i=0;i<tableAttendance.getRowCount();i++)
			{
				CallableStatement call = ConnectDatabase.connection.prepareCall("{call updateAttendance(?,?,?,?,?)}");
				call.setString(1,tableAttendance.getValueAt(i, 0).toString());
				Date pickDate = sdf.parse(tableAttendance.getValueAt(i, 2).toString());
				call.setDate(2,new java.sql.Date(pickDate.getTime()));
				PreparedStatement prUpdateStatusEmp;
				Date now = sdf.parse(sdf.format(new Date()));
				if((boolean) tableAttendance.getValueAt(i, 3))
				{
					call.setInt(3,1);
					if(pickDate.compareTo(now) == 0)
					{
						prUpdateStatusEmp = ConnectDatabase.connection.prepareStatement("UPDATE Employee SET Status_Id = ? WHERE Login_Name = ?");
						prUpdateStatusEmp.setInt(1,2);
						prUpdateStatusEmp.setString(2,tableAttendance.getValueAt(i, 0).toString());
						prUpdateStatusEmp.execute();
					}
				}
				else {
					call.setInt(3,0);
					if(pickDate.compareTo(now) == 0)
					{
						tableAttendance.setValueAt("",i, 4);
						PreparedStatement pr = ConnectDatabase.connection.prepareStatement("SELECT Before_Status FROM Attendance WHERE Login_Name = ?");
						pr.setString(1,tableAttendance.getValueAt(i, 0).toString());
						ResultSet resultStatus = pr.executeQuery();
						int status = -1;
						while(resultStatus.next())
						{
							status = resultStatus.getInt(1);
						}
						if(status != 2)
						{
							prUpdateStatusEmp = ConnectDatabase.connection.prepareStatement("UPDATE Employee SET Status_Id = ? WHERE Login_Name = ?");
							prUpdateStatusEmp.setInt(1,status);
							prUpdateStatusEmp.setString(2,tableAttendance.getValueAt(i, 0).toString());
							prUpdateStatusEmp.execute();
						}
					}
				}
				if(!tableAttendance.getValueAt(i, 4).toString().equals(""))
				{
					if(!tableAttendance.getValueAt(i, 4).toString().contains("-"))
					{
						Date dateTemp = new SimpleDateFormat("EEE MMM d HH:mm:ss z yyyy").parse(tableAttendance.getValueAt(i, 4).toString());
						String strDate = sdf.format(dateTemp);
						Date untilDay = sdf.parse(strDate);
						call.setDate(4,new java.sql.Date(untilDay.getTime()));
					}
					else {
						Date untilDay = sdf.parse(tableAttendance.getValueAt(i, 4).toString());
						call.setDate(4,new java.sql.Date(untilDay.getTime()));
					}
				} else call.setDate(4,null);
				if(tableAttendance.getValueAt(i, 5) != null)
					call.setString(5,tableAttendance.getValueAt(i, 5).toString());
				else call.setString(5, "");
				call.execute();
			}
		} catch(Exception ex) {
			System.out.println(ex.toString());
		}
	}
	
	public static void setAttendance()
	{
			try
			{
	            for (String loginName : listLoginName) {
	            	CallableStatement call = ConnectDatabase.connection.prepareCall("{call setAttendanceTable(?,?,?,?)}");
	            	call.setString(1, loginName);
	    			DateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");  
	    			Date untilDay = formatter.parse(formatter.format(new Date().getTime()));
	                java.sql.Date sqlDateuntil = new java.sql.Date(untilDay.getTime());
	                call.setDate(2,sqlDateuntil);
	                call.setDate(3, null); 
	                call.setString(4,""); 
	                call.execute();
//	                PreparedStatement pr = ConnectDatabase.connection.prepareStatement("UPDATE TempTable SET NowDate = ?");
//	                pr.setDate(1, sqlDateuntil);
//	                pr.execute();
	            }
			} catch(Exception ex)
			{
				System.out.println(ex.toString());
			}
	}
	
	public static void addEmpInAttendance(String loginName)
	{
		try
		{
            	CallableStatement call = ConnectDatabase.connection.prepareCall("{call setAttendanceTable(?,?,?,?)}");
            	call.setString(1, loginName);
    			DateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");  
    			Date untilDay = formatter.parse(formatter.format(new Date().getTime()));
                java.sql.Date sqlDateuntil = new java.sql.Date(untilDay.getTime());
                call.setDate(2,sqlDateuntil);
                call.setDate(3, null); 
                call.setString(4,""); 
                call.execute();
                PreparedStatement pr = ConnectDatabase.connection.prepareStatement("UPDATE TempTable SET NowDate = ?");
                pr.setDate(1, sqlDateuntil);
                pr.execute();
		} catch(Exception ex)
		{
			System.out.println(ex.toString());
		}
	}
	
	public static void getAttendance(JTable tableAttendance,String loginName,Date date)
	{
		DefaultTableModel model = new DefaultTableModel(){
			@Override
			public boolean isCellEditable(int row,int col){
			if(col==0||col==1||col==2){
				return false;
			}
			return true;
		}
	};
		Vector colName = new Vector(); 
		String arrHeader [] ={"Login name", "Function","Attendance day","Absence","On leave until day","Note"};
        for(int i =0; i < arrHeader.length;i++){
      	 colName.addElement(arrHeader[i]); 
        }
        model.setColumnIdentifiers(colName);
		try
		{
			 DateFormat sdf = new SimpleDateFormat("dd-MM-yyyy");
			//Statement stm = ConnectDatabase.connection.createStatement();
			PreparedStatement pr = ConnectDatabase.connection.prepareStatement("SELECT Attendance.Login_Name,Emp_Decentralization.Decentralization_Name,Absence_Date,Absence,Until_Day,Note FROM Employee,Attendance,Emp_Decentralization " +
					"WHERE Attendance.Login_Name = Employee.Login_Name AND Emp_Decentralization.Decentralization_Id = Employee.Decentralization_Id AND Absence_Date = ?");
			String formatDate = sdf.format(date);
			Date currentDate = sdf.parse(formatDate);
			pr.setDate(1,new java.sql.Date(currentDate.getTime()));
			ResultSet rs = pr.executeQuery();
			while(rs.next())
			{
				if(!rs.getString(1).equals(loginName))
				{
					Vector row = new Vector();
					row.add(rs.getString(1));
					row.add(rs.getString(2));
					String resultDate = sdf.format(new java.sql.Date(rs.getDate(3).getTime()));
					row.add(resultDate);
					int absence = rs.getInt(4);
					if(absence == 1)
					{
						row.add(true);
					}
					else row.add(false);
					String resultUntilDay;
					if(rs.getDate(5) != null)
						resultUntilDay = sdf.format(new java.sql.Date(rs.getDate(5).getTime()));
					else resultUntilDay = "";
					row.addElement(resultUntilDay);
					row.add(rs.getString(6));
					model.addRow(row);
				}
			}
		} catch(Exception ex)
		{
			System.out.println(ex.toString());
		}
		tableAttendance.setModel(model);
		TableColumn tc1 = tableAttendance.getColumnModel().getColumn(3);     
        tc1.setCellEditor(tableAttendance.getDefaultEditor(Boolean.class));     
        tc1.setCellRenderer(tableAttendance.getDefaultRenderer(Boolean.class));
        TableColumn colDate = tableAttendance.getColumnModel().getColumn(4); 
        colDate.setCellEditor(new DatePickerCellEditor(new SimpleDateFormat("dd-MM-yyyy")));
        colDate.setCellRenderer(new Support.FormattedCellRenderer(new SimpleDateFormat("dd-MM-yyyy")));
	}
	
	public static void getAllAttendance(JTable tableAttendance,String loginName)
	{
		DefaultTableModel model = new DefaultTableModel(){
			@Override
			public boolean isCellEditable(int row,int col){
			if(col==0||col==1||col==2){
				return false;
			}
			return true;
		}
	};
		Vector colName = new Vector(); 
		String arrHeader [] ={"Login name", "Function","Attendance day","Absence","On leave until day","Note"};
        for(int i =0; i < arrHeader.length;i++){
      	 colName.addElement(arrHeader[i]); 
        }
        model.setColumnIdentifiers(colName);
		try
		{
			PreparedStatement pr = ConnectDatabase.connection.prepareStatement("SELECT Attendance.Login_Name,Emp_Decentralization.Decentralization_Name,Absence_Date,Absence,Until_Day,Note FROM Employee,Attendance,Emp_Decentralization " +
					"WHERE Attendance.Login_Name = Employee.Login_Name AND Emp_Decentralization.Decentralization_Id = Employee.Decentralization_Id");
			ResultSet rs = pr.executeQuery();
			while(rs.next())
			{
				if(!rs.getString(1).equals(loginName))
				{
					Vector row = new Vector();
					row.add(rs.getString(1));
					row.add(rs.getString(2));
					String absenceDay;
					if(rs.getDate(3) != null)
						absenceDay = new SimpleDateFormat("dd-MM-yyyy").format(new java.sql.Date(rs.getDate(3).getTime()));
					else absenceDay = "";
					row.addElement(absenceDay);
					int absence = rs.getInt(4);
					if(absence == 1)
						row.add(true);
					else row.add(false);
					String resultUntilDay;
					if(rs.getDate(5) != null)
					{
						resultUntilDay = new SimpleDateFormat("dd-MM-yyyy").format(new java.sql.Date(rs.getDate(5).getTime()));
					}
					else resultUntilDay = "";
					row.addElement(resultUntilDay);
					row.add(rs.getString(6));
					model.addRow(row);
				}
			}
		} catch(Exception ex)
		{
			System.out.println(ex.toString());
		}
		tableAttendance.setModel(model);
		TableColumn tc1 = tableAttendance.getColumnModel().getColumn(3);     
        tc1.setCellEditor(tableAttendance.getDefaultEditor(Boolean.class));     
        tc1.setCellRenderer(tableAttendance.getDefaultRenderer(Boolean.class));
        TableColumn colDate = tableAttendance.getColumnModel().getColumn(4); 
        colDate.setCellEditor(new DatePickerCellEditor(new SimpleDateFormat("dd-MM-yyyy")));
        colDate.setCellRenderer(new Support.FormattedCellRenderer(new SimpleDateFormat("dd-MM-yyyy")));
	}
	
	public static String findAttendance(JTable tableAttendance,String loginName,String loginNameFind,int function,JXDatePicker datePicker,int absence)
	{
		String formatDate = null;
		DefaultTableModel model = new DefaultTableModel(){
			@Override
			public boolean isCellEditable(int row,int col){
			if(col==0||col==1||col==2){
				return false;
			}
			return true;
		}
	};
		Vector colName = new Vector(); 
		String arrHeader [] ={"Login name", "Function","Attendance day","Absence","On leave until day","Note"};
        for(int i =0; i < arrHeader.length;i++){
      	 colName.addElement(arrHeader[i]); 
        }
        model.setColumnIdentifiers(colName);
        DateFormat sdf = new SimpleDateFormat("dd-MM-yyyy");
        try
        {
        	CallableStatement call = ConnectDatabase.connection.prepareCall("{call findAttendance(?,?,?,?)}");
        	if(!loginNameFind.equals(""))
        	{
        		call.setString(1,loginNameFind);
        	}
        	else call.setString(1,"");
        	call.setInt(2,function);
        	if(datePicker.getDate() != null)
        	{
	        	formatDate = sdf.format(datePicker.getDate());
	 			Date date = sdf.parse(formatDate);
	 			call.setDate(3,new java.sql.Date(date.getTime()));
        	}
 			else call.setString(3,"");
        	if(absence == 0)
        		call.setInt(4, -1);
        	else
        	{
        		if(absence == 2)
        			call.setInt(4, 0);
        		else call.setInt(4, 1);
        	}
 			call.execute();
 			Statement stm = ConnectDatabase.connection.createStatement();
 			ResultSet rs = stm.executeQuery("SELECT Login_Name, Decentralization_Name,Absence_Date ,Absence, Until_Day, Note FROM ##ResultAttendanceTable,Emp_Decentralization WHERE ##ResultAttendanceTable.Decentralization_Id = Emp_Decentralization.Decentralization_Id");
 			while(rs.next())
			{
				if(!rs.getString(2).equals(loginName))
				{
					Vector row = new Vector();
					row.add(rs.getString(1));
					row.add(rs.getString(2));
					String absenceDay;
					if(rs.getDate(3) != null)
						absenceDay = sdf.format(new java.sql.Date(rs.getDate(3).getTime()));
					else absenceDay = "";
					row.addElement(absenceDay);
					int getAbsence = rs.getInt(4);
					if(getAbsence == 1)
						row.add(true);
					else row.add(false);
					String resultUntilDay;
					if(rs.getDate(5) != null)
						resultUntilDay = sdf.format(new java.sql.Date(rs.getDate(5).getTime()));
					else resultUntilDay = "";
					row.addElement(resultUntilDay);
					row.add(rs.getString(6));
					model.addRow(row);
				}
				else System.out.println("Wrong");
			}
 			stm.execute("DROP TABLE ##ResultAttendanceTable");
        } catch(Exception ex) {
        	System.out.println(ex.toString());
        }
        tableAttendance.setModel(model);
		TableColumn tc1 = tableAttendance.getColumnModel().getColumn(3);     
        tc1.setCellEditor(tableAttendance.getDefaultEditor(Boolean.class));     
        tc1.setCellRenderer(tableAttendance.getDefaultRenderer(Boolean.class));
        TableColumn colDate = tableAttendance.getColumnModel().getColumn(4); 
        colDate.setCellEditor(new DatePickerCellEditor(new SimpleDateFormat("dd-MM-yyyy")));
        colDate.setCellRenderer(new Support.FormattedCellRenderer(new SimpleDateFormat("dd-MM-yyyy")));
        return formatDate;
	}
	
	//==============Check sent mail ===================
	
	
		//list all from Lost_Connection
		public static void List_notSent( DefaultTableModel modelTable, JTable table,int option,JTextField loginname,JXDatePicker date){
			try{
				CallableStatement call = ConnectDatabase.connection.prepareCall("{call showNotSentTable(?,?,?)}");
				call.setInt(1, option);
				call.setString(2,loginname.getText());
				DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");  
				if(date.getDate() != null)
				{			
		            Date getdateCreated = formatter.parse(formatter.format(date.getDate()));
		            java.sql.Date showdateCreated = new java.sql.Date(getdateCreated.getTime());
					call.setDate(3,showdateCreated);
					} else call.setString(3,"");
					ResultSet rs = call.executeQuery();
					ResultSetMetaData meta = rs.getMetaData();	
		;			String arrHeaderConnection[] = {"ID", "Login name","Email","Date created","Send"};
			        Vector colName = new Vector();	           	           
			        for (int i = 0; i < arrHeaderConnection.length; i++)
			        {
			            colName.addElement( arrHeaderConnection[i]);
			        }	          	           
			        modelTable.setColumnIdentifiers(colName);
					while(rs.next()){				
						Vector row = new Vector();
											
						row.addElement(rs.getString(1));	
						row.addElement(rs.getString(2));
						row.addElement(rs.getString(3));
								
						Date getdate = (Date)formatter.parse(rs.getString(4));
				        SimpleDateFormat newFormat = new SimpleDateFormat("dd-MM-yyyy");
				        String showDate = newFormat.format(getdate);
				        row.addElement(showDate);
						int conn = rs.getInt(5);
						if(conn==1)
							row.add(true);
						else
							row.add(false);
						modelTable.addRow(row);
					
				}					
				table.setModel(modelTable);
				TableColumn tc1 = table.getColumnModel().getColumn(4);     
		        tc1.setCellEditor(table.getDefaultEditor(Boolean.class));     
		        tc1.setCellRenderer(table.getDefaultRenderer(Boolean.class));       
			}catch(Exception e){
				System.out.println(e.toString());
			}
		}
		
		//add Lost_Connection
		public static void AddConn(String loginname){
			try{
				CallableStatement call = ConnectDatabase.connection.prepareCall("{call AddConnection(?)}");
				call.setString(1, loginname);
				call.execute();
			}catch(Exception e){
				System.out.println(e.toString());
			}
			
		}
		//delete from Lost_Connection
		public static void DeleteConn(String loginname){
			try{
				PreparedStatement prepare = ConnectDatabase.connection.prepareStatement("DELETE FROM Lost_Connection WHERE Login_Name = ?");
				prepare.setString(1, loginname);
				prepare.execute();
			}catch(Exception e){
				System.out.println(e.toString());
			}
		}
		
		//Send Mail again
		
		
		public static void Sent(String loginname, String pass){
			try{
				PreparedStatement preUpt = ConnectDatabase.connection.prepareStatement("UPDATE Employee SET Login_Password = ? WHERE Login_Name = ?");
				preUpt.setString(1, pass);
				preUpt.setString(2,loginname );
				preUpt.execute();
				PreparedStatement preDel = ConnectDatabase.connection.prepareStatement("DELETE FROM Lost_Connection WHERE Login_Name = ?");
				preDel.setString(1, loginname);
				preDel.execute();
			}catch(Exception e){
				System.out.println(e.toString());
			}
		}
	
	//============procedure for courier==================
	public static void AddCou(Courier cr){
		
		try {
			CallableStatement addcou = ConnectDatabase.connection.prepareCall("{Call ADD_COURIER(?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
			
			addcou.setString(1, cr.getCourier_name());
			addcou.setString(2, cr.getS_name());
			addcou.setString(3, cr.getS_address());
			addcou.setDouble(4, cr.getS_phone());
			addcou.setString(5, cr.getR_name());
			addcou.setString(6, cr.getR_address());
			addcou.setDouble(7, cr.getR_phone());
			addcou.setFloat(8, cr.getAmount());
			addcou.setInt(9, cr.getStatus());
			addcou.setString(10, cr.getLogin_name());
			addcou.setFloat(11, cr.getWeight());
			addcou.setInt(12, cr.getS_province());
			addcou.setInt(13, cr.getR_province());
			addcou.setDate(14, (java.sql.Date) cr.getDate());
			addcou.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	

	public static void DelCou(String C_Name){
		try {
			CallableStatement delcou = ConnectDatabase.connection.prepareCall("{Call DELETE_COURIER(?)}");
			delcou.setString(1, C_Name );
			
			delcou.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public static void Update_dis(String C_Name, int status){
		try {

			
			CallableStatement updatecou = ConnectDatabase.connection.prepareCall("{Call UPDATE_DISPATCHING_DEPARTMENT(?,?)}");
			updatecou.setString(1, C_Name);
			updatecou.setInt(2, status);
			updatecou.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static Courier Info_Cou(String C_Name){
		Courier cr = new Courier();
		try {
			CallableStatement infocou = ConnectDatabase.connection.prepareCall("{Call INFO_COURIER(?)}");
			infocou.setString(1, C_Name);
		
			  ResultSet result =infocou.executeQuery();
		      ResultSetMetaData meta = result.getMetaData();
		      while(result.next()){
		    	  cr.courier_name = result.getString(2);
		    	  cr.Datecou=result.getDate(3);
		    	  cr.login_name= result.getString(4);
		    	  cr.s_name = result.getString(5);
		    	  cr.s_address= result.getString(6);
		    	  cr.s_phone = result.getDouble(7);
		    	  cr.r_name= result.getString(8);
		    	  cr.r_address=result.getString(9);
		    	  cr.r_phone= result.getDouble(10);
		    	  cr.status=result.getInt(11);
		    	  cr.amount=result.getFloat(12);
		    	  cr.Weight=result.getFloat(13);
		    	  cr.s_province=result.getInt(14);
		    	  cr.r_province=result.getInt(15);
		      }
		     
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 return cr;
	}
	public static void Update_Wor(Courier cr){
          try {
			
	          CallableStatement updatework = ConnectDatabase.connection.prepareCall("{Call UPDATE_WORKING_DEPARTMENT(?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
	          updatework.setString(1, cr.getCourier_name());
	          updatework.setDate(2, (java.sql.Date) cr.getDate());
	          updatework.setString(3, cr.getLogin_name());
	          updatework.setString(4, cr.getS_name());
	          updatework.setString(5, cr.getS_address());
	          updatework.setDouble(6, cr.getS_phone());
	          updatework.setString(7, cr.getR_name());
	          updatework.setString(8, cr.getR_address());
	          updatework.setDouble(9, cr.getR_phone());
	          updatework.setInt(10, cr.getStatus());
	          updatework.setFloat(11, cr.getAmount());
	          updatework.setFloat(12, cr.getWeight());
	          updatework.setInt(13, cr.getS_province());
	          updatework.setInt(14, cr.getR_province());
	          updatework.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println(e.toString());
		}

          
	}
	//SHOW INFO BY ID
	public static void show_info_byCouName(JTable tbshow, String C_Name){
		
		try {
			DefaultTableModel tablemodelfind = new DefaultTableModel(){
				public boolean isCellEditable(int row, int col){
					return false;
				}
			};
		    CallableStatement show_info = ConnectDatabase.connection.prepareCall("{Call FIND_COURIER_BY_NAME(?)}");
		    show_info.setString(1, C_Name);
	          ResultSet result = show_info.executeQuery();
	          ResultSetMetaData meta = result.getMetaData();
	          Vector<String> colName = new Vector<String>();
	          //String arrHeader [] ={"Courier Name","Sender Name", "Reciver Name","Login Name"};
	          for(int i =0; i < International.arrHeaderCouriers.length;i++){
	        	 colName.addElement(International.arrHeaderCouriers[i]); 
	          }
//          DefaultTableModel tablemodelfind = new DefaultTableModel();
	          tablemodelfind.setColumnIdentifiers(colName);
	          while(result.next()){
	        	  Vector<String> data = new Vector<String>();
	        	  String Cou_Name = result.getString("Courier_Name");
	        	  String Se_name = result.getString("Sender_Name");
	        	  String Re_name = result.getString("Receiver_name");
	        	  String Login_Name=result.getString("Login_Name");
	        	  data.addElement(Cou_Name);
	        	  data.addElement(Se_name);
	        	  data.addElement(Re_name);
	        	  data.addElement(Login_Name);
	        	  tablemodelfind.addRow(data);
	        	  
	          }
	          tbshow.setModel(tablemodelfind);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    
	}
	//get courier that has not done yet
	public static void get_Cou_has_not_done(JTable tbshow){
		try {
			
		    CallableStatement show_info = ConnectDatabase.connection.prepareCall("{Call GET_COUR_HAS_NOT_DONE}");
	          ResultSet result = show_info.executeQuery();
	          ResultSetMetaData meta = result.getMetaData();
	          Vector<String> colName = new Vector<String>();
	          //String arrHeader [] ={"Courier Name","Sender Name", "Reciver Name","Login Name"};
	          for(int i =0; i < International.arrHeaderCouriers.length;i++){
	        	 colName.addElement(International.arrHeaderCouriers[i]); 
	          }
	          DefaultTableModel modelTable = new DefaultTableModel(){
					public boolean isCellEditable(int row, int col){
						return false;
					}
				};
				modelTable.setColumnIdentifiers(colName);
	          while(result.next()){
	        	  Vector<String> data = new Vector<String>();
	        	  String Cou_Name = result.getString("Courier_Name");
	        	  String Se_name = result.getString("Sender_Name");
	        	  String Re_name = result.getString("Receiver_name");
	        	  String Login_Name=result.getString("Login_Name");
	        	  data.addElement(Cou_Name);
	        	  data.addElement(Se_name);
	        	  data.addElement(Re_name);
	        	  data.addElement(Login_Name);
	        	  modelTable.addRow(data);
	        	  
	          }
	          tbshow.setModel(modelTable);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	// SHOW ALL COURIER
	public static void show_all (JTable tbshow){
		
		try {
			 DefaultTableModel tablemodelfind = new DefaultTableModel(){
					public boolean isCellEditable(int row, int col){
						return false;
					}
				};
		    CallableStatement show_info = ConnectDatabase.connection.prepareCall("{Call GET_ALL}");
	          ResultSet result = show_info.executeQuery();
	          ResultSetMetaData meta = result.getMetaData();
	          Vector<String> colName = new Vector<String>();
	          String arrHeader [] ={"Courier Name","Sender Name", "Reciver Name","Login Name"};
	          for(int i =0; i < arrHeader.length;i++){
	        	 colName.addElement(arrHeader[i]); 
	          }
	          tablemodelfind.setColumnIdentifiers(colName);
	          while(result.next()){
	        	  Vector<String> data = new Vector<String>();
	        	  String Cou_Name = result.getString("Courier_Name");
	        	  String Se_name = result.getString("Sender_Name");
	        	  String Re_name = result.getString("Receiver_name");
	        	  String Login_Name=result.getString("Login_Name");
	        	  data.addElement(Cou_Name);
	        	  data.addElement(Se_name);
	        	  data.addElement(Re_name);
	        	  data.addElement(Login_Name);
	        	  tablemodelfind.addRow(data);
	          }
	          tbshow.setModel(tablemodelfind);
		} catch (SQLException e) {
			System.out.println(e.toString());
		}
    
	}
	// show info courier
	public static void show_info2 (JTable tbshow, String S_name, String R_name, String Login_name){
	
          try {
        	  DefaultTableModel tablemodelfind = new DefaultTableModel(){
					public boolean isCellEditable(int row, int col){
						return false;
					}
				};
	          CallableStatement showinfo = ConnectDatabase.connection.prepareCall("{Call FINDING(?,?,?)}");
	          showinfo.setString(1, S_name);
	          showinfo.setString(2,R_name);
	          showinfo.setString(3,Login_name);
	          ResultSet result = showinfo.executeQuery();
	          ResultSetMetaData meta = result.getMetaData();
	          Vector<String> colName = new Vector<String>();
	          String arrHeader [] ={"Courier Name","Sender Name", "Reciver Name","Login Name"};
	          for(int i =0; i < arrHeader.length;i++){
	        	 colName.addElement(arrHeader[i]); 
	          }
	       //   DefaultTableModel tablemodelfind = new DefaultTableModel();
	          tablemodelfind.setColumnIdentifiers(colName);
	          while(result.next()){
	        	  Vector<String> data = new Vector<String>();
	        	  String C_name = result.getString("Courier_Name");
	        	  String Se_name = result.getString("Sender_Name");
	        	  String Re_name = result.getString("Receiver_name");
	        	  String Login_Name=result.getString("Login_Name");
	        	  data.addElement(C_name);
	        	  data.addElement(Se_name);
	        	  data.addElement(Re_name);
	        	  data.addElement(Login_Name);
	        	  tablemodelfind.addRow(data);
	        	  
	          }
	          tbshow.setModel(tablemodelfind);
          }catch(SQLException e){
        	  System.out.println(e.toString());
          }
	
	}
	public static void show_info (JTable tbshow, String S_name, String R_name, String Login_name, java.sql.Date date){
		
        try {
        	DefaultTableModel tablemodelfind = new DefaultTableModel(){
				public boolean isCellEditable(int row, int col){
					return false;
				}
			};
	          CallableStatement showinfo = ConnectDatabase.connection.prepareCall("{Call FIND(?,?,?,?)}");
	          showinfo.setString(1, S_name);
	          showinfo.setString(2,R_name);
	          showinfo.setString(3,Login_name);
	          showinfo.setDate(4, date);
	          ResultSet result = showinfo.executeQuery();
	          ResultSetMetaData meta = result.getMetaData();
	          Vector<String> colName = new Vector<String>();
	          String arrHeader [] ={"Courier Name","Sender Name", "Reciver Name","Login Name"};
	          for(int i =0; i < arrHeader.length;i++){
	        	 colName.addElement(arrHeader[i]); 
	          }
	     //     DefaultTableModel tablemodelfind = new DefaultTableModel();
	          tablemodelfind.setColumnIdentifiers(colName);
	          while(result.next()){
	        	  Vector<String> data = new Vector<String>();
	        	  String C_name = result.getString("Courier_Name");
	        	  String Se_name = result.getString("Sender_Name");
	        	  String Re_name = result.getString("Receiver_name");
	        	  String Login_Name=result.getString("Login_Name");
	        	  data.addElement(C_name);
	        	  data.addElement(Se_name);
	        	  data.addElement(Re_name);
	        	  data.addElement(Login_Name);
	        	  tablemodelfind.addRow(data);
	        	  
	          }
	          tbshow.setModel(tablemodelfind);
        }catch(SQLException e){
      	  e.toString();
        }
	
	}
	// checking whether this courier exists
    public static int check_courier(String C_ID){
    	int found=0;
          try {
			
	          CallableStatement check_C = ConnectDatabase.connection.prepareCall("{Call CHECK_COURIER(?,?)}");
	          check_C.setString(1,C_ID);
	          check_C.registerOutParameter(2,java.sql.Types.INTEGER);
	          check_C.execute();
	          found= check_C.getInt(2);
	        
    }catch(SQLException e){
    		System.out.println(e.toString());
  	  }
          return found;
    }

	//show list location
	public static Vector<String> showLocations()
	{
		Vector<String> listLocations = new Vector<String>();
		try
		{
			Statement statement = ConnectDatabase.connection.createStatement();
	        ResultSet resultSet = statement.executeQuery("SELECT Point.Point_Name FROM Point");
	        while(resultSet.next())
	        {
	        	listLocations.add(resultSet.getString("Point_Name"));
	        }
		} catch(Exception ex)
		{
			System.out.println(ex.toString());
		}
		return listLocations;
	}

	//show list roads
	public static DefaultTableModel showRoads()
	{
		DefaultTableModel modelTable = new DefaultTableModel();
		try
		{
			Vector<String> col = new Vector<String>();
			 for(int i =0; i < International.arrHeaderRoad.length;i++){
				 col.addElement(International.arrHeaderRoad[i]); 
	          }
	        modelTable.setColumnIdentifiers(col);
			Statement statement = ConnectDatabase.connection.createStatement();
	        ResultSet resultSet = statement.executeQuery("SELECT Road_Id,A.Point_Name,Point.Point_Name,Distance FROM (SELECT * FROM Point,Road WHERE Point_A=Point_Id) AS A,Point WHERE Point_B = Point.Point_Id");
	        while(resultSet.next())
	        {
	        	Vector<String> row = new Vector<String>();
	        	row.add(Integer.toString(resultSet.getInt(1)));
	        	row.add(resultSet.getString(2));
	        	row.add(resultSet.getString(3));
	        	row.add(resultSet.getString(4));
	        	modelTable.addRow(row);
	        }
		} catch(Exception ex) {
		}
		return modelTable;
	}
	//UPDATE DISTANCE
	public static void UpdateDistance(float distance, int id){
		try {
			CallableStatement update = ConnectDatabase.connection.prepareCall("{Call UPDATE_DISTANCE(?,?)}");
			update.setFloat(1, distance );
			update.setInt(2, id);
			update.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	//get distance
	public static float getdistance(int S_province, int R_province){
		float dis=0; 
		try {

			CallableStatement distance = ConnectDatabase.connection.prepareCall("{Call GET_DISTANCE(?,?,?)}");
			distance.setInt(1, S_province);
			distance.setInt(2, R_province);
			distance.registerOutParameter(3, java.sql.Types.FLOAT);
			distance.execute();
			dis=distance.getFloat(3);

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
			return dis;
	}
	//ADD DISTANCE
	public static void add_distance(int S_province, int R_province, float distance){
		try {
			CallableStatement add_dt = ConnectDatabase.connection.prepareCall("{Call ADD_DISTANCE(?,?,?)}");
			add_dt.setInt(1, S_province);
			add_dt.setInt(2, R_province);
			add_dt.setFloat(3, distance);
			add_dt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
	}
	//ADD CITY
	public static void add_city(String location){
		try {
			int temp=0;
			CallableStatement check = ConnectDatabase.connection.prepareCall("{Call CHECK_LOCATION (?,?)}");
			check.setString(1, location);
			check.registerOutParameter(2,java.sql.Types.INTEGER);
			check.execute();
			temp =check.getInt(2);
			if(temp==1) JOptionPane.showMessageDialog(null, "It already has this city");
			else{
				CallableStatement add_location = ConnectDatabase.connection.prepareCall("{Call ADD_LOCATION(?)}");
				add_location.setString(1, location);
				add_location.execute();
				JOptionPane.showMessageDialog(null, "Adding successful");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	//show the trade
	public static DefaultTableModel showTrade()
	{
		DefaultTableModel modelTable = new DefaultTableModel();
		try
		{
			Vector<String> col = new Vector<String>();
			col.addElement("Distance (kms)");
			col.addElement("Rate upto 2 kg (INR)");
			col.addElement("Additional kg (INR)");
			String id[] = new String[] {"Local","<500km","501 - 1000km","1001-2000km","above 2000km" };
			int i=0;
	        modelTable.setColumnIdentifiers(col);
			Statement statement = ConnectDatabase.connection.createStatement();
	        ResultSet resultSet = statement.executeQuery("SELECT * FROM CourierDatabase.dbo.Charges");
	        while(resultSet.next())
	        {
	        	Vector<String> row = new Vector<String>();
	        //	row.add(Integer.toString(resultSet.getInt(1)));
	        	row.add(id[i]);
	        	row.add(Float.toString(resultSet.getFloat(2)));
	        	row.add(Float.toString(resultSet.getFloat(3)));
	        	modelTable.addRow(row);
	        	i++;
	        }
		} catch(Exception ex) {
		}
		return modelTable;
	}
	//get distance index
	public static int GetDistanceIndex(float distance,int S_province, int R_province){
		int temp=0;
		try {
			CallableStatement dis_index = ConnectDatabase.connection.prepareCall("{Call CALULATE(?,?,?,?)}");
			dis_index.setFloat(1, distance);
			dis_index.registerOutParameter(2, java.sql.Types.INTEGER);
			dis_index.setInt(3, S_province);
			dis_index.setInt(4, R_province);
			dis_index.execute();
			temp=dis_index.getInt(2);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		return temp;
	}
	//update trade
	public static void UpdateTrade(int distance, float price, float addition){
		try {
			CallableStatement update = ConnectDatabase.connection.prepareCall("{Call UPDATE_TRADE(?,?,?)}");
			update.setFloat(1, price);
			update.setFloat(2, addition);
			update.setInt(3, distance);
			update.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

